-----------------------------------------------------
-- @name: optimizer_index_caching_index_cost_adj
-- @author: dion cho
-- @description: test for optimizer_index_caching and optimizer_index_cost_adj parameter
-----------------------------------------------------

drop table t1 purge;
drop table t2 purge;
create table t1(c1 char(10), c2 char(10));
create table t2(c1 char(10), c2 char(10));

insert into t1
select level, 'x'
from dual
connect by level <= 10000
;

insert into t2
select level, 'x'
from dual
connect by level <= 10000
;

create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);

exec dbms_stats.gather_table_stats(user, 't1');
exec dbms_stats.gather_table_stats(user, 't2');

alter session set "_optimizer_cost_model" = io;


alter session set optimizer_index_cost_adj = 100;

alter session set optimizer_index_caching = 0;

explain plan for
select /*+ use_nl(t1 t2) */ *
from t1, t2
where t1.c1 = t2.c1
;

@plan

explain plan for
select /*+ index(t1) */ *
from t1 where c1 > ' '
;

@plan

alter session set optimizer_index_caching = 25;

explain plan for
select /*+ use_nl(t1 t2) */ *
from t1, t2
where t1.c1 = t2.c1
;

@plan

explain plan for
select /*+ index(t1) */ *
from t1 where c1 > ' '
;

@plan


alter session set optimizer_index_caching = 50;

explain plan for
select /*+ use_nl(t1 t2) */ *
from t1, t2
where t1.c1 = t2.c1
;

@plan

explain plan for
select /*+ index(t1) */ *
from t1 where c1 > ' '
;

@plan

alter session set optimizer_index_caching = 75;

explain plan for
select /*+ use_nl(t1 t2) */ *
from t1, t2
where t1.c1 = t2.c1
;

@plan

explain plan for
select /*+ index(t1) */ *
from t1 where c1 > ' '
;

@plan

alter session set optimizer_index_caching = 100;

explain plan for
select /*+ use_nl(t1 t2) */ *
from t1, t2
where t1.c1 = t2.c1
;

@plan

explain plan for
select /*+ index(t1) */ *
from t1 where c1 > ' '
;

@plan


-- optimizer_index_cost_adj

alter session set optimizer_index_caching = 0;

alter session set optimizer_index_cost_adj = 100;

explain plan for
select /*+ use_nl(t1 t2) */ *
from t1, t2
where t1.c1 = t2.c1
;

@plan

explain plan for
select /*+ index(t1) */ *
from t1 where c1 > ' '
;

@plan


alter session set optimizer_index_cost_adj = 75;

explain plan for
select /*+ use_nl(t1 t2) */ *
from t1, t2
where t1.c1 = t2.c1
;

@plan

explain plan for
select /*+ index(t1) */ *
from t1 where c1 > ' '
;

@plan

alter session set optimizer_index_cost_adj = 50;

explain plan for
select /*+ use_nl(t1 t2) */ *
from t1, t2
where t1.c1 = t2.c1
;

@plan

explain plan for
select /*+ index(t1) */ *
from t1 where c1 > ' '
;

@plan


alter session set optimizer_index_cost_adj = 25;

explain plan for
select /*+ use_nl(t1 t2) */ *
from t1, t2
where t1.c1 = t2.c1
;

@plan

explain plan for
select /*+ index(t1) */ *
from t1 where c1 > ' '
;

@plan

alter session set optimizer_index_cost_adj = 15;

explain plan for
select /*+ use_nl(t1 t2) */ *
from t1, t2
where t1.c1 = t2.c1
;

@plan

explain plan for
select /*+ index(t1) */ *
from t1 where c1 > ' '
;

@plan

alter session set optimizer_index_cost_adj = 1;

explain plan for
select /*+ use_nl(t1 t2) */ *
from t1, t2
where t1.c1 = t2.c1
;

@plan

explain plan for
select /*+ index(t1) */ *
from t1 where c1 > ' '
;

@plan

------------------------------------------------------
-- be careful for the bug     
-- Bug 5578791 - Combination of optimizer_index_caching and optimizer_index_cost_adj increases Cost
alter session set optimizer_index_cost_adj = 100;
alter session set optimizer_index_caching = 0;

explain plan for
select /*+ use_nl(t1 t2) */ *
from t1, t2
where t1.c1 = t2.c1
;

@plan


alter session set optimizer_index_cost_adj = 100;
alter session set optimizer_index_caching = 100;

explain plan for
select /*+ use_nl(t1 t2) */ *
from t1, t2
where t1.c1 = t2.c1
;

@plan

alter session set optimizer_index_cost_adj = 75;
alter session set optimizer_index_caching = 100;

explain plan for
select /*+ use_nl(t1 t2) */ *
from t1, t2
where t1.c1 = t2.c1
;

@plan

alter session set optimizer_index_cost_adj = 50;
alter session set optimizer_index_caching = 100;

explain plan for
select /*+ use_nl(t1 t2) */ *
from t1, t2
where t1.c1 = t2.c1
;

@plan


alter session set optimizer_index_cost_adj = 25;
alter session set optimizer_index_caching = 100;

explain plan for
select /*+ use_nl(t1 t2) */ *
from t1, t2
where t1.c1 = t2.c1
;

@plan



alter session set optimizer_index_cost_adj = 5;
alter session set optimizer_index_caching = 100;

explain plan for
select /*+ use_nl(t1 t2) */ *
from t1, t2
where t1.c1 = t2.c1
;

@plan



spool off
ed result2.txt
